package com.group.hugedatabehind.mapper;

import com.group.hugedatabehind.pojo.MessageTimeDistribution;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface MessageMapper {
    @Select("SELECT " +
            "    CASE " +
            "        WHEN msg_hour BETWEEN 0 AND 6 THEN 'Night(0-6点)' " +
            "        WHEN msg_hour BETWEEN 7 AND 12 THEN 'Morning(7-12点)' " +
            "        WHEN msg_hour BETWEEN 13 AND 18 THEN 'Afternoon(13-18点)' " +
            "        ELSE 'Evening(19-23点)' " +
            "    END AS time_period, " +
            "    COUNT(*) AS msg_cnt " +
            "FROM db_msg.tb_msg_etl " +
            "WHERE message LIKE CONCAT('%', #{message}, '%') " +
            "GROUP BY " +
            "    CASE " +
            "        WHEN msg_hour BETWEEN 0 AND 6 THEN 'Night(0-6点)' " +
            "        WHEN msg_hour BETWEEN 7 AND 12 THEN 'Morning(7-12点)' " +
            "        WHEN msg_hour BETWEEN 13 AND 18 THEN 'Afternoon(13-18点)' " +
            "        ELSE 'Evening(19-23点)' " +
            "    END")
//    @Param("message")：用于指定方法参数的名称，这样可以在 SQL 中通过 #{message} 引用该参数。
    List<MessageTimeDistribution> getMessageTimeDistribution(@Param("message") String message);
}
